﻿

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using MySql.Data.MySqlClient;

public class DAL_tbl_supplier_master
{
    public DAL_tbl_supplier_master()
    {

    }



    public List<tbl_supplier_master> select(String USER_CODE)
    {
        List<tbl_supplier_master> list = new List<tbl_supplier_master>();
        MySqlCommand cmd = new MySqlCommand();
        cmd.CommandText = "Select USER_CODE,SUPPLIER_NAME,SUPPLIER_ADDRESS,SUPPLIER_CITY,SUPPLIER_STATE,SUPPLIER_PINCODE,SUPPLIER_PRIMARY_CONTACT_NO,SUPPLIER_SECONDARY_CONTACT_NUMBER,SUPPLIER_EMAIL_ID,SUPPLIER_SKYPE_ID,Payment_Information, Bank_Account, Supplier_Agreement,Supplier_Agreement_Original_Name, Fax, LinkedIn_ID, Tax_Vat_ID,IS_ACTIVE,CREATED_BY,CREATED_DATE,UPDATED_BY,UPDATED_DATE from tbl_supplier_master where USER_CODE=@USER_CODE";
        cmd.Parameters.AddWithValue("@USER_CODE", USER_CODE);
        DataTable dt = DBUtil.ExecuteDataTable(cmd);
        foreach (DataRow dr in dt.Rows)
        {
            list.Add(new tbl_supplier_master()
            {
                USER_CODE = dr["USER_CODE"].ToString(),
                SUPPLIER_NAME = dr["SUPPLIER_NAME"].ToString(),
                SUPPLIER_ADDRESS = dr["SUPPLIER_ADDRESS"].ToString(),
                SUPPLIER_CITY = dr["SUPPLIER_CITY"].ToString(),
                SUPPLIER_STATE = dr["SUPPLIER_STATE"].ToString(),
                SUPPLIER_PINCODE = dr["SUPPLIER_PINCODE"].ToString(),
                SUPPLIER_PRIMARY_CONTACT_NO = dr["SUPPLIER_PRIMARY_CONTACT_NO"].ToString(),
                SUPPLIER_SECONDARY_CONTACT_NUMBER = dr["SUPPLIER_SECONDARY_CONTACT_NUMBER"].ToString(),
                SUPPLIER_EMAIL_ID = dr["SUPPLIER_EMAIL_ID"].ToString(),
                SUPPLIER_SKYPE_ID = dr["SUPPLIER_SKYPE_ID"].ToString(),

                Payment_Information = dr["Payment_Information"].ToString(),
                Bank_Account = dr["Bank_Account"].ToString(),
                Supplier_Agreement = dr["Supplier_Agreement"].ToString(),
                Supplier_Agreement_Original_Name = dr["Supplier_Agreement_Original_Name"].ToString(),
                Fax = dr["Fax"].ToString(),
                LinkedIn_ID = dr["LinkedIn_ID"].ToString(),
                Tax_Vat_ID = dr["Tax_Vat_ID"].ToString(),

                IS_ACTIVE = Convert.ToBoolean(dr["IS_ACTIVE"]),
                CREATED_BY = dr["CREATED_BY"].ToString(),
                CREATED_DATE = dr["CREATED_DATE"].toDateTimeWithoutError(),
                UPDATED_BY = dr["UPDATED_BY"].ToString(),
                UPDATED_DATE = dr["UPDATED_DATE"].toDateTimeWithoutError()
            });
        }

        return list;
    }



    public List<tbl_supplier_master> selectAll()
    {
        List<tbl_supplier_master> list = new List<tbl_supplier_master>();
        MySqlCommand cmd = new MySqlCommand();
        cmd.CommandText = "Select USER_CODE,SUPPLIER_NAME,SUPPLIER_ADDRESS,SUPPLIER_CITY,SUPPLIER_STATE,SUPPLIER_PINCODE,SUPPLIER_PRIMARY_CONTACT_NO,SUPPLIER_SECONDARY_CONTACT_NUMBER,SUPPLIER_EMAIL_ID,SUPPLIER_SKYPE_ID,Payment_Information, Bank_Account, Supplier_Agreement,Supplier_Agreement_Original_Name , Fax, LinkedIn_ID, Tax_Vat_ID,IS_ACTIVE,CREATED_BY,CREATED_DATE,UPDATED_BY,UPDATED_DATE from tbl_supplier_master Order By USER_CODE";

        DataTable dt = DBUtil.ExecuteDataTable(cmd);
        foreach (DataRow dr in dt.Rows)
        {
            list.Add(new tbl_supplier_master()
            {
                USER_CODE = dr["USER_CODE"].ToString(),
                SUPPLIER_NAME = dr["SUPPLIER_NAME"].ToString(),
                SUPPLIER_ADDRESS = dr["SUPPLIER_ADDRESS"].ToString(),
                SUPPLIER_CITY = dr["SUPPLIER_CITY"].ToString(),
                SUPPLIER_STATE = dr["SUPPLIER_STATE"].ToString(),
                SUPPLIER_PINCODE = dr["SUPPLIER_PINCODE"].ToString(),
                SUPPLIER_PRIMARY_CONTACT_NO = dr["SUPPLIER_PRIMARY_CONTACT_NO"].ToString(),
                SUPPLIER_SECONDARY_CONTACT_NUMBER = dr["SUPPLIER_SECONDARY_CONTACT_NUMBER"].ToString(),
                SUPPLIER_EMAIL_ID = dr["SUPPLIER_EMAIL_ID"].ToString(),
                SUPPLIER_SKYPE_ID = dr["SUPPLIER_SKYPE_ID"].ToString(),

                Payment_Information = dr["Payment_Information"].ToString(),
                Bank_Account = dr["Bank_Account"].ToString(),
                Supplier_Agreement = dr["Supplier_Agreement"].ToString(),
                Supplier_Agreement_Original_Name = dr["Supplier_Agreement_Original_Name"].ToString(),
                Fax = dr["Fax"].ToString(),
                LinkedIn_ID = dr["LinkedIn_ID"].ToString(),
                Tax_Vat_ID = dr["Tax_Vat_ID"].ToString(),

                IS_ACTIVE = Convert.ToBoolean(dr["IS_ACTIVE"]),
                CREATED_BY = dr["CREATED_BY"].ToString(),
                CREATED_DATE = dr["CREATED_DATE"].toDateTimeWithoutError(),
                UPDATED_BY = dr["UPDATED_BY"].ToString(),
                UPDATED_DATE = dr["UPDATED_DATE"].toDateTimeWithoutError()
            });
        }

        return list;
    }



    public int insert(String USER_CODE, String SUPPLIER_NAME, String SUPPLIER_ADDRESS, String SUPPLIER_CITY, String SUPPLIER_STATE, String SUPPLIER_PINCODE, String SUPPLIER_PRIMARY_CONTACT_NO, String SUPPLIER_SECONDARY_CONTACT_NUMBER, String SUPPLIER_EMAIL_ID, String SUPPLIER_SKYPE_ID, String Payment_Information, String Bank_Account, String Supplier_Agreement, String Supplier_Agreement_Original_Name, String Fax, String LinkedIn_ID, String Tax_Vat_ID, bool IS_ACTIVE, String CREATED_BY, DateTime CREATED_DATE)
    {
        int i = 0;
        string sql = @"Insert into tbl_supplier_master(USER_CODE, SUPPLIER_NAME, SUPPLIER_ADDRESS, SUPPLIER_CITY, SUPPLIER_STATE, SUPPLIER_PINCODE, SUPPLIER_PRIMARY_CONTACT_NO, SUPPLIER_SECONDARY_CONTACT_NUMBER, SUPPLIER_EMAIL_ID, SUPPLIER_SKYPE_ID,Payment_Information, Bank_Account, Supplier_Agreement,Supplier_Agreement_Original_Name , Fax, LinkedIn_ID, Tax_Vat_ID, IS_ACTIVE, CREATED_BY, CREATED_DATE)
                        values(@USER_CODE, @SUPPLIER_NAME, @SUPPLIER_ADDRESS, @SUPPLIER_CITY, @SUPPLIER_STATE, @SUPPLIER_PINCODE, @SUPPLIER_PRIMARY_CONTACT_NO, @SUPPLIER_SECONDARY_CONTACT_NUMBER, @SUPPLIER_EMAIL_ID, @SUPPLIER_SKYPE_ID,@Payment_Information, @Bank_Account, @Supplier_Agreement,@Supplier_Agreement_Original_Name , @Fax, @LinkedIn_ID, @Tax_Vat_ID, @IS_ACTIVE, @CREATED_BY, @CREATED_DATE)";

        MySqlCommand cmd = new MySqlCommand(sql);

        cmd.Parameters.AddWithValue("@USER_CODE", USER_CODE);
        cmd.Parameters.AddWithValue("@SUPPLIER_NAME", SUPPLIER_NAME);
        cmd.Parameters.AddWithValue("@SUPPLIER_ADDRESS", SUPPLIER_ADDRESS);
        cmd.Parameters.AddWithValue("@SUPPLIER_CITY", SUPPLIER_CITY);
        cmd.Parameters.AddWithValue("@SUPPLIER_STATE", SUPPLIER_STATE);
        cmd.Parameters.AddWithValue("@SUPPLIER_PINCODE", SUPPLIER_PINCODE);
        cmd.Parameters.AddWithValue("@SUPPLIER_PRIMARY_CONTACT_NO", SUPPLIER_PRIMARY_CONTACT_NO);
        cmd.Parameters.AddWithValue("@SUPPLIER_SECONDARY_CONTACT_NUMBER", SUPPLIER_SECONDARY_CONTACT_NUMBER);
        cmd.Parameters.AddWithValue("@SUPPLIER_EMAIL_ID", SUPPLIER_EMAIL_ID);
        cmd.Parameters.AddWithValue("@SUPPLIER_SKYPE_ID", SUPPLIER_SKYPE_ID);

        cmd.Parameters.AddWithValue("@Payment_Information", Payment_Information);
        cmd.Parameters.AddWithValue("@Bank_Account", Bank_Account);

        cmd.Parameters.AddWithValue("@Supplier_Agreement",Supplier_Agreement);
        cmd.Parameters.AddWithValue("@Supplier_Agreement_Original_Name",Supplier_Agreement_Original_Name);
        cmd.Parameters.AddWithValue("@Fax", Fax);
        cmd.Parameters.AddWithValue("@LinkedIn_ID", LinkedIn_ID);
        cmd.Parameters.AddWithValue("@Tax_Vat_ID", Tax_Vat_ID);

        cmd.Parameters.AddWithValue("@IS_ACTIVE", IS_ACTIVE);
        cmd.Parameters.AddWithValue("@CREATED_BY", CREATED_BY);
        cmd.Parameters.AddWithValue("@CREATED_DATE", CREATED_DATE);

        i = DBUtil.ExecuteNonQuery(cmd);
        return i;
    }



    public int update(String USER_CODE, String SUPPLIER_NAME, String SUPPLIER_ADDRESS, String SUPPLIER_CITY, String SUPPLIER_STATE, String SUPPLIER_PINCODE, String SUPPLIER_PRIMARY_CONTACT_NO, String SUPPLIER_SECONDARY_CONTACT_NUMBER, String SUPPLIER_EMAIL_ID, String SUPPLIER_SKYPE_ID, String Payment_Information, String Bank_Account, String Supplier_Agreement, String Supplier_Agreement_Original_Name, String Fax, String LinkedIn_ID, String Tax_Vat_ID, bool IS_ACTIVE, String UPDATED_BY, DateTime UPDATED_DATE)
    {
        int i = 0;
        string sql = @"Update tbl_supplier_master SET SUPPLIER_NAME=@SUPPLIER_NAME, SUPPLIER_ADDRESS=@SUPPLIER_ADDRESS, SUPPLIER_CITY=@SUPPLIER_CITY, SUPPLIER_STATE=@SUPPLIER_STATE, SUPPLIER_PINCODE=@SUPPLIER_PINCODE, SUPPLIER_PRIMARY_CONTACT_NO=@SUPPLIER_PRIMARY_CONTACT_NO, SUPPLIER_SECONDARY_CONTACT_NUMBER=@SUPPLIER_SECONDARY_CONTACT_NUMBER, SUPPLIER_EMAIL_ID=@SUPPLIER_EMAIL_ID, SUPPLIER_SKYPE_ID=@SUPPLIER_SKYPE_ID,Payment_Information=@Payment_Information, Bank_Account=@Bank_Account, Supplier_Agreement=@Supplier_Agreement,Supplier_Agreement_Original_Name=@Supplier_Agreement_Original_Name, Fax=@Fax, LinkedIn_ID=@LinkedIn_ID, Tax_Vat_ID=@Tax_Vat_ID, IS_ACTIVE=@IS_ACTIVE, UPDATED_BY=@UPDATED_BY, UPDATED_DATE=@UPDATED_DATE WHERE USER_CODE=@USER_CODE";

        MySqlCommand cmd = new MySqlCommand(sql);

        cmd.Parameters.AddWithValue("@USER_CODE", USER_CODE);
        cmd.Parameters.AddWithValue("@SUPPLIER_NAME", SUPPLIER_NAME);
        cmd.Parameters.AddWithValue("@SUPPLIER_ADDRESS", SUPPLIER_ADDRESS);
        cmd.Parameters.AddWithValue("@SUPPLIER_CITY", SUPPLIER_CITY);
        cmd.Parameters.AddWithValue("@SUPPLIER_STATE", SUPPLIER_STATE);
        cmd.Parameters.AddWithValue("@SUPPLIER_PINCODE", SUPPLIER_PINCODE);
        cmd.Parameters.AddWithValue("@SUPPLIER_PRIMARY_CONTACT_NO", SUPPLIER_PRIMARY_CONTACT_NO);
        cmd.Parameters.AddWithValue("@SUPPLIER_SECONDARY_CONTACT_NUMBER", SUPPLIER_SECONDARY_CONTACT_NUMBER);
        cmd.Parameters.AddWithValue("@SUPPLIER_EMAIL_ID", SUPPLIER_EMAIL_ID);
        cmd.Parameters.AddWithValue("@SUPPLIER_SKYPE_ID", SUPPLIER_SKYPE_ID);

        cmd.Parameters.AddWithValue("@Payment_Information", Payment_Information);
        cmd.Parameters.AddWithValue("@Bank_Account", Bank_Account);
        cmd.Parameters.AddWithValue("@Supplier_Agreement", Supplier_Agreement);
        cmd.Parameters.AddWithValue("@Supplier_Agreement_Original_Name", Supplier_Agreement_Original_Name);
        cmd.Parameters.AddWithValue("@Fax", Fax);
        cmd.Parameters.AddWithValue("@LinkedIn_ID", LinkedIn_ID);
        cmd.Parameters.AddWithValue("@Tax_Vat_ID", Tax_Vat_ID);

        cmd.Parameters.AddWithValue("@IS_ACTIVE", IS_ACTIVE);
        cmd.Parameters.AddWithValue("@UPDATED_BY", UPDATED_BY);
        cmd.Parameters.AddWithValue("@UPDATED_DATE", UPDATED_DATE);

        i = DBUtil.ExecuteNonQuery(cmd);
        return i;
    }



    public int delete(String USER_CODE)
    {
        int i = 0;
        string sql = "Delete FROM tbl_supplier_master WHERE USER_CODE=@USER_CODE";

        MySqlCommand cmd = new MySqlCommand(sql);

        cmd.Parameters.AddWithValue("@USER_CODE", USER_CODE);

        i = DBUtil.ExecuteNonQuery(cmd);
        return i;
    }



}


public class DAL_tbl_supplier_master_Certificate
{

    public int update(String USER_CODE, string CERTIFICATION_FILE, string CERTIFICATION_FILE_ORIGINAL_NAME, DateTime CERTIFICATION_DATE, string UPDATED_BY, DateTime UPDATED_DATE, String Supplier_Agreement, String Supplier_Agreement_Original_Name)
    {
        int i = 0;
        string sql = @"Update tbl_supplier_master SET CERTIFICATION_FILE=@CERTIFICATION_FILE, CERTIFICATION_FILE_ORIGINAL_NAME=@CERTIFICATION_FILE_ORIGINAL_NAME, CERTIFICATION_DATE=@CERTIFICATION_DATE, UPDATED_BY=@UPDATED_BY, UPDATED_DATE=@UPDATED_DATE, Supplier_Agreement=@Supplier_Agreement, Supplier_Agreement_Original_Name=@Supplier_Agreement_Original_Name WHERE USER_CODE=@USER_CODE";

        MySqlCommand cmd = new MySqlCommand(sql);

        cmd.Parameters.AddWithValue("@USER_CODE", USER_CODE);
        cmd.Parameters.AddWithValue("@CERTIFICATION_FILE", CERTIFICATION_FILE);
        cmd.Parameters.AddWithValue("@CERTIFICATION_FILE_ORIGINAL_NAME", CERTIFICATION_FILE_ORIGINAL_NAME);
        cmd.Parameters.AddWithValue("@CERTIFICATION_DATE", CERTIFICATION_DATE);
        cmd.Parameters.AddWithValue("@UPDATED_BY", UPDATED_BY);
        cmd.Parameters.AddWithValue("@UPDATED_DATE", UPDATED_DATE);
        cmd.Parameters.AddWithValue("@Supplier_Agreement", Supplier_Agreement);
        cmd.Parameters.AddWithValue("@Supplier_Agreement_Original_Name", Supplier_Agreement_Original_Name);

        i = DBUtil.ExecuteNonQuery(cmd);
        return i;
    }

    public List<tbl_supplier_master_Certificate> select(String USER_CODE)
    {
        List<tbl_supplier_master_Certificate> list = new List<tbl_supplier_master_Certificate>();
        MySqlCommand cmd = new MySqlCommand();
        cmd.CommandText = "Select USER_CODE,CERTIFICATION_FILE,CERTIFICATION_FILE_ORIGINAL_NAME,CERTIFICATION_DATE, Supplier_Agreement, Supplier_Agreement_Original_Name from tbl_supplier_master where USER_CODE=@USER_CODE";
        cmd.Parameters.AddWithValue("@USER_CODE", USER_CODE);
        DataTable dt = DBUtil.ExecuteDataTable(cmd);
        foreach (DataRow dr in dt.Rows)
        {
            list.Add(new tbl_supplier_master_Certificate()
            {
                USER_CODE = dr["USER_CODE"].ToString(),
                CERTIFICATION_FILE = dr["CERTIFICATION_FILE"].ToString(),
                CERTIFICATION_FILE_ORIGINAL_NAME = dr["CERTIFICATION_FILE_ORIGINAL_NAME"].ToString(),
                CERTIFICATION_DATE = dr["CERTIFICATION_DATE"].toDateTimeWithoutError(),
                Supplier_Agreement = dr["Supplier_Agreement"].ToString(),
                Supplier_Agreement_Original_Name = dr["Supplier_Agreement_Original_Name"].ToString()
            });
        }

        return list;
    }
}